package basics;

import org.apache.commons.collections4.list.TreeList;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

/**
 * @Author: zhenzhong.wang
 * @Time: 2018/5/17 15:16
 */
public class ExcelUtils {

  List<String> userSqlList = new TreeList<>();
  List<String> profileSqlList = new TreeList<>();
  List<String> roleSqlList = new TreeList<>();
  List<String> appUserSqlList = new TreeList<>();

  HashMap<String, String> roleMap = new HashMap<>();
  HashMap<String, String> groupMap = new HashMap<>();

  {
    roleMap.put("超级管理员", "1");
    roleMap.put("管理员", "2");
    roleMap.put("普通用户", "3");

    groupMap.put("HyperS", "1");
    groupMap.put("Nielsen", "2");
    groupMap.put("SaaS", "3");
  }

  String userTemplate = "INSERT INTO `user`(`id`, `name`, `email`, `password`, `status`, `creator`, `group_id`, `language`," +
      " `create_time`, `last_login_time`, `update_time`) " + "VALUES (re-userId, 're-username', 're-email', '1a597874e698eab88cbceae352c90a04398f9f8d134fe10f7bd8e722eb4cecb0'," +
      " 1, null, re-groupId, 1, '2017-11-22 17:51:54', '2017-11-24 15:26:05', '2017-11-24 15:24:43');";
  String profileTemplate = "INSERT INTO `user_profile`(`user_id`, `full_name`, `avatar`, `avatar_thumb`, `theme`, " +
      "`phone`, `gender`, `industry`, `company`, `position`, `create_time`, `update_time`)" + " VALUES " +
      "(re-userId, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2016-10-09 17:20:41', '2016-10-09 17:20:41');";
  String roleTemplate = "INSERT INTO `user_role_rel`(`role_id`, `user_id`, `status`) VALUES (re-roleId, re-userId, 1);";
  String appUserTemplate = "INSERT INTO `app_user_rel`(`app_id`, `user_id`, `status`, `email_notify`) VALUES (re-appId, re-userId, 1, 1);";

  /**
   * 得到Excel，并解析内容，对2007及以上版本 使用XSSF解析
   *
   * @throws IOException
   * @throws InvalidFormatException
   */
  @Test
  public void SQLGenerator() throws IOException, InvalidFormatException {
    String filePath = "C:\\Users\\ZZWang\\Desktop\\工作\\HAC\\HAC环境接入\\尼尔森 - 独立部署\\HAC接入新环境Checklist-Nielsen-0510.xlsx";
    // 通过流创建XSSF WorkBook
    InputStream ins = new FileInputStream(new File(filePath));
    XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(ins);

    // 目标sheet初始化
    Sheet sheet = wb.getSheetAt(1);
    for (int i = 1; i <= 33; i++) {
      Row row = sheet.getRow(i);

      String username = row.getCell(0).getStringCellValue().trim();
      String email = row.getCell(1).getStringCellValue().trim();
      String password = "1a597874e698eab88cbceae352c90a04398f9f8d134fe10f7bd8e722eb4cecb0";
      String groupId = groupMap.get(row.getCell(4).getStringCellValue().trim());
      String roleId = roleMap.get(row.getCell(3).getStringCellValue().trim());
      String userId = String.valueOf(i + 1);

      // 创建模板
      String userInsert = this.userTemplate;
      String profileInsert = this.profileTemplate;
      String roleInsert = this.roleTemplate;

      // 创建用户插入语句
      userInsert = userInsert.replaceAll("re-userId", String.valueOf(userId));
      userInsert = userInsert.replaceAll("re-username", username);
      userInsert = userInsert.replaceAll("re-email", email);
//    try {
      userInsert = userInsert.replaceAll("re-groupId", groupId);
//    } catch (NullPointerException e) {
//    System.out.println("错误：" + username);
//    }
      userSqlList.add(userInsert);

      // 创建profile插入语句
      profileInsert = profileInsert.replaceAll("re-userId", userId);
      profileSqlList.add(profileInsert);

      // 创建role插入语句
      roleInsert = roleInsert.replaceAll("re-roleId", roleId);
      roleInsert = roleInsert.replaceAll("re-userId", userId);
      roleSqlList.add(roleInsert);

      // 创建appUser插入语句
      for (int j = 1; j <= 7; j++) {
        if (j == 1 || j == 3 || j == 4 || j == 6 || j == 7) {
          String appUserInsert = this.appUserTemplate;
          appUserInsert = appUserInsert.replaceAll("re-userId", userId);
          appUserInsert = appUserInsert.replaceAll("re-appId", String.valueOf(j));
          appUserSqlList.add(appUserInsert);
        }
      }
    }

    userSqlList.forEach(e -> {
      System.out.println(e);
    });
    System.out.println();

    profileSqlList.forEach(e -> {
      System.out.println(e);
    });
    System.out.println();

    roleSqlList.forEach(e -> {
      System.out.println(e);
    });
    System.out.println();

    appUserSqlList.forEach(e -> {
      System.out.println(e);
    });

    wb.close();
    ins.close();
  }
}
